package netbay;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigInteger;
import java.nio.channels.Channel;
import java.nio.channels.Channels;
import java.nio.channels.FileChannel;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import util.jQgrid.JQGridRow;

public class UserManager {
	public static String jdbcDriver = "";
	public static String dbUrl = "";
	public static String dbUser = "";
	public static String dbPassword = "";
	public static String superAdmin= "";
	public static String passwordSuperAdmin;
	public static String isAble;
	public static String actionLogin = "";
	public static String actionLogout = "";
	public static String actionAddNewGroup = "";
	public static String actionEditGroup = "";
	public static String actionDeleteGroup = "";
	public static String actionAddNewUser = "";
	public static String actionEditUser = "";
	public static String actionDeleteUser = "";
	public static String actionEditProfile = "";
	public static String actionReTCDTCD = "";
	public static String actionReJPMCTCD = "";
	public static String actionReRDRD = "";
	public static String actionReJPMCRD = "";
	

	public UserManager(){
		super();
	}
	
	public List getDisplayPermission(String page,
			String limit, String sidx, String sord) throws Exception {

		Connection con = null;
		List resultList = null;
		try {

			con = getConnection();
			PreparedStatement st = UserGroup.convertFindPermission(page, limit, sidx, sord,con);

			if (st != null) {
				ResultSet rs = st.executeQuery();
				resultList = UserGroup.convertResultsetPermissionToDisplay(rs);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception("Cannot display group.");
			//return null;
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return resultList;
	}
		public List getDisplayPermission() throws Exception {
		Connection con = null;
		List resultList = null;
		try {

			con = getConnection();
			PreparedStatement st = UserGroup.convertFindPermission(con);

			if (st != null) {
				ResultSet rs = st.executeQuery();
				resultList = UserGroup.convertResultsetPermissionToDisplay(rs);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception("Cannot display group.");
			//return null;
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return resultList;
	}	
		
		public List getDisplayUserLog(String fromTime,String toTime,String page,
				String limit, String sidx, String sord) {

			Connection con = null;
			List resultList = null;
			try {

				con = getConnection();
				PreparedStatement st = UserGroup.convertFindUserLog(fromTime,toTime,page, limit, sidx, sord,
						con);

				if (st != null) {
					ResultSet rs = st.executeQuery();

					resultList = UserGroup
							.convertResultsetUserLogToDisplay(rs);
				}
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			} finally {
				if (con != null) {
					try {
						con.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}

			return resultList;
		}
		
		
		public Integer getCountUserLog(String fromTime,String toTime) {

			Connection con = null;
			Integer count = 0;
			try {

				con = getConnection();
				PreparedStatement st = UserGroup.convertCountUserLog(fromTime,toTime,
						con);

				if (st != null) {
					ResultSet rs = st.executeQuery();
					if(rs.next()){
						count  = rs.getInt("sum");
					}
					
				}
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			} finally {
				if (con != null) {
					try {
						con.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}

			return count;
		}
		
		
		
		public String getUsernameByid(Integer id) {
			Connection con = null;
			String result = "";
			try {

				con = getConnection();
				PreparedStatement st = UserGroup.sqlGetUsernameById(id,con);

				if (st != null) {
					ResultSet rs = st.executeQuery();
					if(rs.next()){
						result=rs.getString("username");
					}
				}

			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			} finally {
				if (con != null) {
					try {
						con.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}

			return result;
		}	
		
		public String getGroupById(Integer id) {
			Connection con = null;
			String result = "";
			try {

				con = getConnection();
				PreparedStatement st = UserGroup.sqlGetGroupById(id,con);

				if (st != null) {
					ResultSet rs = st.executeQuery();
					if(rs.next()){
						result=rs.getString("name");
					}
				}

			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			} finally {
				if (con != null) {
					try {
						con.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}

			return result;
		}	
		
		public boolean deleteAdmin(String rowdata) {
			Connection con = null;
			try {

				con = getConnection();
				PreparedStatement st = UserGroup.deleteAdmin(rowdata, con);
				if (st != null) {
					st.executeUpdate();

					return true;
				}

			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			} finally {
				if (con != null) {
					try {
						con.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}

			return false;

		}

	
	public Boolean savePermission(int id,int tcdTracking,int rdTracking,int tcdBilling,int rdBilling,
			int manageUsers,int manageGroups,int userLog,
			int reTCDTCD,int reJPMCTCD,int reRDRD,int reJPMCRD) {
		Connection con = null;
		
		try {

			con = getConnection();
			PreparedStatement st = UserGroup.sqlSavePermission(con,id,tcdTracking,rdTracking,tcdBilling,rdBilling,manageUsers,manageGroups,userLog,reTCDTCD,reJPMCTCD,reRDRD,reJPMCRD);

			if (st != null) {
				st.executeUpdate();
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return false;
	}
	
	public boolean deleteGroup(Integer id) {
		Connection con = null;
		try {
			con = getConnection();
			PreparedStatement st = UserGroup.sqlDeleteGroup(id, con);
			if (st != null) {
				st.executeUpdate();

				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return false;

	}
	
	public boolean deleteUserByGroup(Integer id) throws Exception {
		Connection con = null;
		try {
			con = getConnection();
			PreparedStatement st = UserGroup.sqlDeleteUserByGroup(id, con);
			if (st != null) {
				st.executeUpdate();

				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception("Cannot delete group.");
			//return false;
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return false;

	}
	
	public boolean checkGroup(String group) {
		Connection con = null;
		try {
			con = getConnection();
			PreparedStatement st = UserGroup.sqlCheckGroup(group, con);
			if (st != null) {
				ResultSet rs = st.executeQuery();
				if (!(rs.next()))
					return true;
				else
					return false;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return false;

	}
	
	public Boolean insertNewGroup(String group,int tcdTracking,int rdTracking,int tcdBilling,int rdBilling,
			int manageUsers,int manageGroups,int userLog,
			int reTCDTCD,int reJPMCTCD,int reRDRD,int reJPMCRD) {
		
		Connection con = null;
		
		try {

			con = getConnection();
			PreparedStatement st = UserGroup.sqlInsertNewGroup(con,group,tcdTracking,rdTracking,tcdBilling,rdBilling,manageUsers,manageGroups,userLog,reTCDTCD,reJPMCTCD,reRDRD,reJPMCRD);

			if (st != null) {
				st.executeUpdate();
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return false;
	}
	
	public List displayUser(String page,
			String limit, String sidx, String sord) throws Exception {

		Connection con = null;
		List resultList = null;
		try {

			con = getConnection();
			PreparedStatement st = UserGroup.sqlDisplayUser(page, limit, sidx, sord,con);

			if (st != null) {
				ResultSet rs = st.executeQuery();
				resultList = UserGroup.convertResultsetUserToDisplay(rs);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception("Cannot display user.");
			//return null;
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return resultList;
	}
	
	public List displayUser() throws Exception {

		Connection con = null;
		List resultList = null;
		try {

			con = getConnection();
			PreparedStatement st = UserGroup.sqlDisplayUser(con);

			if (st != null) {
				ResultSet rs = st.executeQuery();
				resultList = UserGroup.convertResultsetUserToDisplay(rs);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception("Cannot display user.");
			//return null;
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return resultList;
	}
	
	public Connection getConnection() throws SQLException {
		try {
			Class.forName(jdbcDriver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		Connection con = DriverManager.getConnection(dbUrl
				+ "?useUnicode=true&characterEncoding=utf-8", dbUser,
				dbPassword);
		return con;
	}
	
	public List getDataUserById(Integer id) {
		Connection con = null;
		List resultList = null;
		try {
			con = getConnection();
			PreparedStatement st = UserGroup.sqlGetDataUserById(id, con);
			if (st != null) {
				ResultSet rs = st.executeQuery();
				resultList = UserGroup.convertResultsetUserDataToObject(rs);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return resultList;

	}
	
	public boolean checkOldPassword(Integer id,String oldpass) {
		Connection con = null;
		try {
			
			con = getConnection();
			PreparedStatement st = UserGroup.sqlCheckOldPassword(id,oldpass, con);
			if (st != null) {
				ResultSet rs = st.executeQuery();
				if ((rs.next()))
					return true;
				else
					return false;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return false;

	}
	public boolean editUser(Integer id,String oldpassword,String password,String hashPassword,String rdoRecordType,String firstname,String lastname,
			String address,String tel,String fax,String email){
		Connection con = null;
		try {
			
			con = getConnection();
			PreparedStatement st = UserGroup.sqlEditUser(id,oldpassword,password,hashPassword,rdoRecordType,firstname,lastname,address,tel,fax,email, con);
			if (st != null) {
				st.executeUpdate();
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			
			return false;
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return false;

	}
	
	
	public boolean checkUsername(String username) {
		Connection con = null;
		try {

			con = getConnection();
			PreparedStatement st = UserGroup.checkUsername(username, con);
			if (st != null) {
				ResultSet rs = st.executeQuery();
				if (!(rs.next()))
					return true;
				else
					return false;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return false;

	}
	
	public boolean addNewUser(String username, String password,Integer id ,String rdoRecordType,
			String firstname,String lastname,String address, 
			String tel,String fax,String email) {
		Connection con = null;
		try {

			con = getConnection();
			PreparedStatement st = UserGroup.sqlAddNewUser(username, password, id, rdoRecordType, firstname, lastname, address, tel, fax, email,
					con);
			if (st != null) {
				st.executeUpdate();

				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return false;

	}
	
	
	public List getLoginVerify(String username,String hashPassword, String password) throws Exception {
		Connection con = null;
		List resultList = null;
		
		if(username.equals(superAdmin) && password.equals(passwordSuperAdmin) && isAble.equals("1")){
			ArrayList<String> list=new ArrayList<String>();
			list.add("0");
			list.add(superAdmin);
			list.add("1");
			list.add("1");
			list.add("1");
			list.add("1");
			list.add("1");
			list.add("1");
			list.add("1");
			list.add("1");
			list.add("1");
			list.add("1");
			list.add("1");
			return list;
		}else{
			try {
				con = getConnection();
				PreparedStatement st = UserGroup.loginVerify(username,
						hashPassword, con);
				if (st != null) {
					ResultSet rs = st.executeQuery();
					resultList = UserGroup.convertResultsetPermissionToList(rs);
				}
			} catch (SQLException e) {
				e.printStackTrace();
				throw new Exception("Verify fail");
				//return resultList;
			} finally {
				if (con != null) {
					try {
						con.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
		return resultList;

	}
	
	public ArrayList<String> loadDdlGroup() {
		Connection con = null;
		ArrayList<String> resultList=new ArrayList<String>();
		try {

			con = getConnection();
			PreparedStatement st = UserGroup.sqlLoadDdlGroup(con);

			if (st != null) {
				ResultSet rs = st.executeQuery();
				resultList = UserGroup.convertResultsetDdlGroupToDisplay(rs);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return resultList;
	}	
	
	public void saveLog(String userid,String action,String reference) throws Exception {
		Connection con = null;
		try {

			con = getConnection();
			PreparedStatement st = UserGroup.sqlLog(userid,action,reference, con);
			if (st != null) {
				st.executeUpdate();
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception("Cannot save log.");
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	public static String hashPassword(String password) {
		String hashword = null;
			MessageDigest md5 = null;
			try {
				md5 = MessageDigest.getInstance("MD5");
			} catch (NoSuchAlgorithmException e) {
				e.printStackTrace();
			}
			md5.update(password.getBytes());
			BigInteger hash = new BigInteger(1, md5.digest());
			hashword = hash.toString(16);
		if(hashword.length() == 31){
			hashword = "0"+hashword;	
			}
		return hashword;
	}
}

